Imports System.Collections.Generic
Imports System.Text
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient

Public Class AbstractDAO
	''' <summary>
	''' cac bien thanh phan thuc hien thao tac truy xuat CSDL
	''' </summary>
	Protected sql As [String]
	'bien giu vai tro la cau lenh truy van
	Protected objConnection As SqlConnection
	Protected objCommand As SqlCommand
	Protected objDataAdapter As SqlDataAdapter
	Protected objDataReader As SqlDataReader
	'''///////////////////////////////////////////////////////////////////////
	''' <summary>
	''' truyen duong dan toi thuc muc chua database de thuc thi ket noi
	''' </summary>
	''' <param name="strPath"></param>
	Public Sub New(strPath As [String])
		Try
			Dim objConnectionString As String = strPath

			objConnection = New SqlConnection(objConnectionString)
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		End Try
	End Sub
	''' <summary>
	''' Add parameter cho cau lenh truy van
	''' </summary>
	''' <param name="Name"></param> 
	''' <param name="Type"></param>
	''' <param name="Size"></param>
	''' <param name="Value"></param>
	Public Sub AddParameter(Name As String, Type As SqlDbType, Size As Integer, Value As Object)
		Try
			objCommand.Parameters.Add(Name, Type, Size).Value = Value
		Catch OleDbExceptionErr As System.Exception
			Throw New System.Exception(OleDbExceptionErr.Message, OleDbExceptionErr.InnerException)
		End Try
	End Sub
	''' <summary>
	''' Add parameter, thuc hien viec lay gia tri duoc tra ve tu store
	''' </summary>
	''' <param name="Name"></param>
	''' <param name="Type"></param>
	''' <param name="Size"></param>
	''' <param name="Value"></param>
	Public Sub AddParameterWithDirection(Name As String, Type As SqlDbType, Size As Integer)
		Try
			Dim param As SqlParameter = objCommand.CreateParameter()
			param.ParameterName = Name
			param.Direction = ParameterDirection.Output
			param.SqlDbType = Type
			param.Size = Size
			objCommand.Parameters.Add(param)
		Catch OleDbExceptionErr As System.Exception
			Throw New System.Exception(OleDbExceptionErr.Message, OleDbExceptionErr.InnerException)
		End Try
	End Sub
	Public Function GetParamValue(Name As String) As Object
		Return objCommand.Parameters(Name).Value
	End Function
	''' <summary>
	''' mo ket noi
	''' </summary>
	Public Sub OpenConnection()
		Try
			objConnection.Open()
		Catch ExceptionErr As SqlException
			Throw New Exception(ExceptionErr.Message, ExceptionErr.InnerException)
		Catch InvalidOperationExceptionErr As InvalidOperationException
			Throw New Exception(InvalidOperationExceptionErr.Message, InvalidOperationExceptionErr.InnerException)
		End Try
	End Sub
	''' <summary>
	''' dong ket noi
	''' </summary>
	Public Sub CloseConnection()
		objConnection.Close()
	End Sub
	''' <summary>
	''' Khoi tao cau lenh truy van
	''' </summary>
	Public Sub InitializeCommand()
		If objCommand Is Nothing Then
			Try
				objCommand = New SqlCommand(sql, objConnection)
				'See if this is a stored procedure
				If Not sql.ToUpper().StartsWith("SELECT ") AndAlso Not sql.ToUpper().StartsWith("INSERT ") AndAlso Not sql.ToUpper().StartsWith("UPDATE ") AndAlso Not sql.ToUpper().StartsWith("DELETE ") Then
                    objCommand.CommandType = CommandType.StoredProcedure
                Else
                    objCommand.CommandType = CommandType.Text
                End If
			Catch e As System.Exception
				Throw New Exception(e.Message, e.InnerException)
			End Try
		End If
	End Sub
	Public Sub InitializeDataAdapter()
		Try
			objDataAdapter = New SqlDataAdapter()
				'lua chon cau lenh
			objDataAdapter.SelectCommand = objCommand
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		End Try
	End Sub
	''' <summary>
	''' do du lieu vao dataset
	''' </summary>
	''' <param name="daoDataSet"></param>
	''' <param name="TableName"></param>
	Public Sub FillDataSet(ByRef daoDataSet As DataSet, TableName As [String])
		Try
			InitializeCommand()
			InitializeDataAdapter()
			objDataAdapter.Fill(daoDataSet, TableName)
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		End Try
	End Sub

	Public Sub CleanUpObject()
		objCommand.Dispose()
		objCommand = Nothing
		objDataAdapter.Dispose()
		objDataAdapter = Nothing
	End Sub
	''' <summary>
	''' do du lieu vao dataTable
	''' </summary>
	''' <param name="daoDataSet"></param>
	''' <param name="TableName"></param>
	Public Sub FillDataTable(ByRef oDataTable As DataTable)
		Try
			InitializeCommand()
			InitializeDataAdapter()
			objDataAdapter.Fill(oDataTable)
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		Finally
			objCommand.Dispose()
			objCommand = Nothing
			objDataAdapter.Dispose()
			objDataAdapter = Nothing
		End Try
	End Sub
	''' <summary>
	''' thuc thi cau truy van
	''' </summary>
	''' <returns>thuc thi xong, dong ket noi tra ve 1 bao thanh cong</returns>
	Public Function ExecuteStoredProcedure() As [Boolean]
		Try
			OpenConnection()
			If objCommand.ExecuteNonQuery() <> 0 Then
				CloseConnection()
				Return True
			Else
				CloseConnection()
					'that bai
				Return False
			End If
		Catch e As System.Exception
			Throw New Exception(e.Message, e.InnerException)
		End Try
	End Function
End Class
